// Inspired by: http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/
package photo.navi;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class LocationDatabaseManager extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "PhotoNaviDB";
	private static final int DATABASE_VERSION = 1;
    private static final String TABLE_LOCATIONS = "locations";
    
    private static final String KEY_ID = "id";
    private static final String KEY_GPS_LOCATION = "gps_location";
    private static final String KEY_PHOTO_PATH = "photo_path";
    private static final String KEY_DESCRIPTION = "description";
    
	public LocationDatabaseManager(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String create_table = "CREATE TABLE " + TABLE_LOCATIONS + "("
				+ KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ KEY_GPS_LOCATION + " TEXT NOT NULL, "
				+ KEY_PHOTO_PATH + " TEXT NOT NULL, "
			    + KEY_DESCRIPTION + " TEXT NOT NULL)";
        db.execSQL(create_table);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int old_version, int new_version) {
		/* Recreate table on upgrade. The best policy ever. */
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_LOCATIONS);
        onCreate(db);
	}
	
	// POST location
	public void addPhotoLocation(PhotoLocation photo_location) {
		SQLiteDatabase db = this.getWritableDatabase();
		 
	    ContentValues values = new ContentValues();
	    values.put(KEY_GPS_LOCATION, photo_location.getGpsLocation());
	    values.put(KEY_PHOTO_PATH, photo_location.getPhotoPath());
	    values.put(KEY_DESCRIPTION, photo_location.getDescription());
	 
	    db.insert(TABLE_LOCATIONS, null, values);
	    db.close(); // Closing database connection
	}
	 
	// GET location
	public PhotoLocation getPhotoLocation(int id) {
		SQLiteDatabase db = this.getReadableDatabase();
		 
	    Cursor cursor = db.query(TABLE_LOCATIONS, new String[] { KEY_ID,
	            KEY_GPS_LOCATION, KEY_PHOTO_PATH, KEY_DESCRIPTION}, KEY_ID + "=?",
	            new String[] { String.valueOf(id) }, null, null, null, null);
	    if (cursor != null)
	        cursor.moveToFirst();
	 
	    PhotoLocation photo_location= new PhotoLocation(Integer.parseInt(cursor.getString(0)),
	            cursor.getString(1), cursor.getString(2), cursor.getString(3));
	    db.close();
	    // return location
	    return photo_location;
	}
	 
	// GET locations
	public List<PhotoLocation> getAllPhotoLocations() {
		List<PhotoLocation> locationList = new ArrayList<PhotoLocation>();
	    // Select All Query
	    String selectQuery = "SELECT  * FROM " + TABLE_LOCATIONS;
	 
	    SQLiteDatabase db = this.getReadableDatabase();
	    Cursor cursor = db.rawQuery(selectQuery, null);
	 
	    // looping through all rows and adding to list
	    if (cursor.moveToFirst()) {
	        do {
	            PhotoLocation photo_location= new PhotoLocation();
	            photo_location.setID(Integer.parseInt(cursor.getString(0)));
	            photo_location.setGpsLocation(cursor.getString(1));
	            photo_location.setPhotoPath(cursor.getString(2));
	            photo_location.setDescription(cursor.getString(3));
	            // Adding contact to list
	            locationList.add(photo_location);
	        } while (cursor.moveToNext());
	    }
	    cursor.close();
	    db.close();
	    // return contact list
	    return locationList;
	}
	 
	// COUNT locations
	public int getPhotoLocationsCount() {
		String countQuery = "SELECT  * FROM " + TABLE_LOCATIONS;
	    SQLiteDatabase db = this.getReadableDatabase();
	    Cursor cursor = db.rawQuery(countQuery, null);
	    int count = cursor.getCount();
	    cursor.close();
	    db.close();
	    // return count
	    return count;
    }
	
	// PUT location
	public void updatePhotoLocation(PhotoLocation photo_location) {
		SQLiteDatabase db = this.getWritableDatabase();
		 
	    ContentValues values = new ContentValues();
	    values.put(KEY_GPS_LOCATION, photo_location.getGpsLocation());
	    values.put(KEY_PHOTO_PATH, photo_location.getPhotoPath());
	    values.put(KEY_DESCRIPTION, photo_location.getDescription());
	 
	    // updating row
	    db.update(TABLE_LOCATIONS, values, KEY_ID + " = ?", new String[] { String.valueOf(photo_location.getID()) });
	    db.close(); // Closing database connection
	}
	 
	// DELETE location
	public void deletePhotoLocation(PhotoLocation photo_location) {
		SQLiteDatabase db = this.getWritableDatabase();
	    db.delete(TABLE_LOCATIONS, KEY_ID + " = ?",
	            new String[] { String.valueOf(photo_location.getID()) });
	    db.close();
	}

}
